#!/usr/bin/env python
# -*- coding: utf-8 -*-

# @Time    : 2019/12/4 20:28

from common.connect_db import MyDB

my_db = MyDB()


class DataBaseCoin:
    """
    微币表的操作
    """

    @staticmethod
    def get_common_coin_all():
        """
        获得
        所有common_coin_coin的列表信息
        :return: 返回列表
        """
        sql = "select * from common_coin_coin"
        result = my_db.execute_select("coin", sql)
        return result

    @staticmethod
    def filter_common_coin_account_info(member_id, account_type, investment_id=0):
        """
        根据三个信息获取用户详细的资产详情
        :param member_id:
        :param account_type: 积分账户类型 1：会员积分账户 2：投资积分账户 3：采购商积分账户
        :param investment_id: 默认为0
        :return: 返回用户对象信息
        """
        sql = "select * from common_coin_coin " \
              "where member_id = '%s' and account_type = '%s' and investment_id = '%s' " \
              "" % (member_id, account_type, investment_id)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError('Data Error')

    @staticmethod
    def filter_common_coin_account_info_by_id(coin_id):
        """
        根据coin_id获取用户详细的资产详情
        :param coin_id:
        :return: 返回用户对象信息
        """
        sql = "select * from common_coin_coin where id = '%s' " % coin_id
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError('Data Error')

    @staticmethod
    def filter_common_coin_account_all(member_id):
        """
        根据用户ID查询该用户的所有资产信息（不包含投资账户）
        :param member_id:
        :return: 返回列表
        """
        sql = "select * from common_coin_coin " \
              "where member_id = '%s' and account_type != 2" % member_id
        result = my_db.execute_select("coin", sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("No Data!")

    @staticmethod
    def filter_common_coin_log_order_info(order_no, child_type):
        """
        根据订单号和流水类型来获取订单的详情信息
        :param order_no: 订单号
        :param child_type:  流水类型，去common_coin_type表中获得
        :return: 返回微币流水的详情信息
        """
        sql = "select * from common_coin_coin_log " \
              "where order_no = '%s' and child_type = '%s'" \
              "" % (order_no, child_type)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("Data Error!")

    @staticmethod
    def assert_common_coin_coin(member_id, account_type, investment_id, status, total_coin, now_coin, freeze_coin,
                                withdraw_coin, today_increase, today_decrease, reserve_coin, version):
        """
        断言common_coin_coin表
        :param member_id:
        :param account_type: 积分账户类型 1：会员积分账户 2：投资积分账户 3：采购商积分账户
        :param investment_id: 除了投资账户其他为0
        :param status: 用户积分账户状态；0：正常，-1：冻结
        :param total_coin:
        :param now_coin:
        :param freeze_coin:
        :param withdraw_coin:
        :param today_increase:
        :param today_decrease:
        :param reserve_coin:
        :param version: 每次操作加 1
        :return: True OR False
        """
        sql = "select * from common_coin_coin " \
              "where member_id = '%s' and account_type = '%s' and investment_id = '%s' " \
              "and status = '%s' " \
              "and total_coin = '%s' and now_coin = '%s' and freeze_coin = '%s' and withdraw_coin = '%s' " \
              "and today_increase = '%s' and today_decrease = '%s' and reserve_coin = '%s' " \
              "and create_time is not null and update_time is not NULL " \
              "and version = '%s'" \
              "" % (member_id, account_type, investment_id, status, total_coin, now_coin, freeze_coin,
                    withdraw_coin, today_increase, today_decrease, reserve_coin, version)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_common_coin_log(coin_id, now_coin, coin, child_type, order_no, remark):
        """
        断言微币日志表
        :return: True OR False
        """
        sql = "select * from common_coin_coin_log " \
              "where coin_id = '%s' and now_coin = '%s' " \
              "and coin = '%s' and child_type = '%s' and order_no = '%s' and remark = '%s' " \
              "" % (coin_id, now_coin, coin, child_type, order_no, remark)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_common_coin_reserve_log(coin_id, coin, now_coin, order_no, status, child_type, remark=""):
        """
        断言微币预留表
        :return: True OR False
        """
        if remark == "":
            sql = "select * from common_coin_reserve_coin_log " \
                  "where coin_id = '%s' and coin = '%s' and now_coin = '%s' " \
                  "and order_no = '%s' and status = '%s' and child_type = '%s'" \
                  " and remark is NULL" \
                  "" % (coin_id, coin, now_coin, order_no, status, child_type)
        else:
            sql = "select * from common_coin_reserve_coin_log " \
                  "where coin_id = '%s' and coin = '%s' and now_coin = '%s' " \
                  "and order_no = '%s' and status = '%s' and child_type = '%s'" \
                  " and remark like '%%%s'" \
                  "" % (coin_id, coin, now_coin, order_no, status, child_type, remark)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_common_coin_reserve_log_change_status(order_no, status):
        """
        判断预留微币订单号是否按照按钮改变了状态！
        :return: True OR False
        """
        sql = "select * from common_coin_reserve_coin_log " \
              "where order_no = '%s' and status = '%s'" % (order_no, status)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def get_user_coin_in_come(user_id, start_time='', end_time=''):
        """
        用户所有微币收入
        :param user_id:
        :param start_time:
        :param end_time:
        :return:
        """
        if start_time == '':
            start_time = '1999-01-01 00:00:00'
        if end_time == '':
            end_time = '2999-12-31 23:59:59'

        sql = "select sum(coin) from common_coin_coin_log " \
              "where coin_id = '%s' and coin > 0 " \
              "and create_time between '%s' and '%s' " \
              "" % (user_id, start_time, end_time)
        print("SQL: " + sql)
        cursor = my_db.execute_sql("coin", sql)
        result = cursor.fetchall()
        print("断言查询：" + str(result))
        if result[0][0]:
            return result[0][0]
        else:
            return 0

    @staticmethod
    def get_user_coin_expenses(user_id, start_time='', end_time=''):
        """
        用户所有微币支出
        :param user_id:
        :param start_time:
        :param end_time:
        :return:
        """
        if start_time == '':
            start_time = '1999-01-01 00:00:00'
        if end_time == '':
            end_time = '2999-12-31 23:59:59'

        sql = "select sum(coin) from common_coin_coin_log " \
              "where coin_id = '%s' and coin < 0 " \
              "and create_time between '%s' and '%s' " \
              "" % (user_id, start_time, end_time)
        result = my_db.execute_select("coin", sql)
        if result[0][0]:
            return result[0][0]
        else:
            return 0

    """
    积分操作
    """

    @staticmethod
    def get_common_credits_list():
        """
        查询common_coin_credits表所有数据
        :return: 返回列表信息
        """
        sql = "select * from common_coin_credits"
        result = my_db.execute_select("coin", sql)
        return result

    @staticmethod
    def filter_common_credits_account_info(member_id, account_type, investment_id=0):
        """
        根据三个信息获取用户详细的资产详情
        :param member_id:
        :param account_type: 积分账户类型 1：会员积分账户 2：投资积分账户 3：采购商积分账户
        :param investment_id: 默认为0
        :return: 返回用户对象信息
        """
        sql = "select * from common_coin_credits " \
              "where member_id = '%s' and account_type = '%s' and investment_id = '%s' " \
              "" % (member_id, account_type, investment_id)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("Data Error!")

    @staticmethod
    def filter_common_credits_order_info(order_no, child_type):
        """
        根据订单号和流水类型来获取订单的详情信息
        :param order_no: 订单号
        :param child_type:  流水类型，去common_coin_type表中获得
        :return: 返回微币流水的详情信息
        """
        sql = "select * from common_coin_credits_log " \
              "where order_no = '%s' and child_type = '%s'" \
              "" % (order_no, child_type)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return result[0]
        else:
            return result

    @staticmethod
    def filter_common_credit_account_info_by_id(credit_id):
        """
        根据credit_id获取用户详细的资产详情
        :param credit_id:
        :return: 返回用户对象信息
        """
        sql = "select * from common_coin_credits where id = '%s' " % credit_id
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError('Data Error')

    @staticmethod
    def assert_common_credits(member_id, account_type, investment_id, status, total_credits, now_credits,
                              today_increase, today_decrease, version, level, transfer_date):
        """
        断言积分表common_coin_credits
        :param member_id:
        :param account_type:
        :param investment_id:
        :param status:
        :param total_credits:
        :param now_credits:
        :param today_increase:
        :param today_decrease:
        :param version:
        :param level:
        :param transfer_date:
        :return: True or False
        """
        sql = "select * from common_coin_credits " \
              "where member_id = '%s' and account_type = '%s' and investment_id = '%s' " \
              "and status = '%s' and total_credits = '%s' and now_credits = '%s' " \
              "and today_increase = '%s' and today_decrease = '%s' " \
              "and create_time is not null and update_time is not NULL " \
              "and version = '%s' and level = '%s' and transfer_date = '%s'" \
              "" % (member_id, account_type, investment_id, status, total_credits, now_credits,
                    today_increase, today_decrease, version, level, transfer_date)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_common_credits_log(credits_id, now_credits, credit, child_type, order_no, remark):
        """
        断言积分日志表
        :param credits_id:
        :param now_credits:
        :param credit:
        :param child_type:
        :param order_no:
        :param remark:
        :return:
        """
        sql = "select * from common_coin_credits_log where credits_id = '%s' and now_credits = '%s' " \
              "and credits = '%s' and child_type = '%s' and order_no = '%s' and remark = '%s' " \
              "and create_time is not NULL  " \
              "" % (credits_id, now_credits, credit, child_type, order_no, remark)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_common_credits_reserve_log(credits_id, credit, now_credits,
                                          order_no, status, child_type, remark=""):
        """
        断言积分预留表
        :return: True OR False
        """
        if remark == "":
            sql = "select * from common_coin_reserve_credits_log " \
                  "where credits_id = '%s' and credits = '%s' and now_credits = '%s' " \
                  "and order_no = '%s' and status = '%s' and child_type = '%s'" \
                  " and remark is NULL" \
                  "" % (credits_id, credit, now_credits, order_no, status, child_type)
        else:
            sql = "select * from common_coin_reserve_credits_log " \
                  "where credits_id = '%s' and credits = '%s' and now_credits = '%s' " \
                  "and order_no = '%s' and status = '%s' and child_type = '%s'" \
                  " and remark like '%s'" \
                  "" % (credits_id, credit, now_credits, order_no, status, child_type, remark)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_common_credit_reserve_log_change_status(order_no, status):
        """
        判断预留微币订单号是否按照按钮改变了状态！
        :return: True OR False
        """
        sql = "select * from common_coin_reserve_credits_log " \
              "where order_no = '%s' and status = '%s'" % (order_no, status)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def get_user_deposit_count(credits_id):
        """
        获取用户积分存入笔数
        :param credits_id:
        :return:
        """
        sql = "select count(*) as count from common_coin_credits_log " \
              "where credits_id = '%s' and credits > 0" % credits_id

        result = my_db.execute_select("coin", sql)
        return result[0][0]

    @staticmethod
    def get_user_deposit_num(credits_id):
        """
        获取用户积分存入数量
        :param credits_id:
        :return:
        """
        sql = "select sum(credits) as sum from common_coin_credits_log " \
              "where credits_id = '%s' and credits > 0" % credits_id

        result = my_db.execute_select("coin", sql)
        return result[0][0]

    @staticmethod
    def get_user_add_credit_log_list(credits_id, limit=9999):
        """
        获得用户所有积分增加的记录
        :param credits_id:
        :param limit:
        :return:
        """
        sql = "select common_coin_credits_log.id," \
              "common_coin_credits_log.credits_id," \
              "common_coin_credits_log.now_credits," \
              "common_coin_credits_log.credits," \
              "common_coin_type.type_code," \
              "common_coin_credits_log.order_no," \
              "common_coin_credits_log.create_time, " \
              "common_coin_credits_log.is_show," \
              "common_coin_credits_log.remark, " \
              "common_coin_type.type_name " \
              "from common_coin_credits_log, common_coin_type " \
              "where common_coin_credits_log.child_type = common_coin_type.type_code " \
              "and common_coin_credits_log.credits_id = '%s' and credits > 0 and is_show = 1 " \
              "order by common_coin_credits_log.create_time desc ,common_coin_credits_log.id desc " \
              "limit %d " % (credits_id, limit)
        result = my_db.execute_select("coin", sql)
        print("断言查询：" + str(result))

        return result

    @staticmethod
    def get_user_coin_log_list(coin_id, limit=9999):
        """
        获得用户所有微币记录
        :param coin_id:
        :param limit:
        :return:
        """
        sql = "select common_coin_coin_log.id," \
              "common_coin_coin_log.coin_id," \
              "common_coin_coin_log.now_coin," \
              "common_coin_coin_log.coin," \
              "common_coin_type.type_code," \
              "common_coin_type.type_name," \
              "common_coin_coin_log.order_no," \
              "common_coin_coin_log.create_time, " \
              "common_coin_coin_log.is_show, " \
              "common_coin_coin_log.remark " \
              "from common_coin_coin_log, common_coin_type " \
              "where common_coin_coin_log.child_type = common_coin_type.type_code " \
              "and common_coin_coin_log.coin_id = '%s'" \
              "order by common_coin_coin_log.create_time desc ,common_coin_coin_log.id desc " \
              "limit %d " % (coin_id, limit)
        print("SQL: " + sql)
        cursor = my_db.execute_sql("coin", sql)
        result = cursor.fetchall()
        print("断言查询：" + str(result))

        return result

    """
    其他
    """

    @staticmethod
    def get_common_coin_type_list():
        """
        获得所有积分微币类型(不包含主类型ABCDE)
        :return: 返回列表
        """
        sql = "select * from common_coin_type " \
              "where type_code!='A' AND type_code!='B' AND type_code!='C' AND type_code!='D' " \
              "AND type_code!='E'"
        print("SQL: " + sql)
        result = my_db.execute_select("coin", sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("No Data!")

    @staticmethod
    def get_common_coin_type_list_all():
        """
        获得所有积分微币类型
        :return: 返回列表
        """
        sql = "select * from common_coin_type"
        result = my_db.execute_select("coin", sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("No Data!")

    @staticmethod
    def filter_common_coin_type_list(type_name_start_letter):
        """
        :param type_name_start_letter: 英文首字母 A:积分新增 B:积分抵扣扣减积分 C:微币新增 D:微币抵扣
        :return:
        """
        sql = "select * from common_coin_type where type_code like '%s%%' and type_code != '%s' " \
              "" % (type_name_start_letter, type_name_start_letter)
        result = my_db.execute_select("coin", sql)
        if len(result) > 0:
            return result
        else:
            raise ValueError("No Data!")

    @staticmethod
    def get_common_coin_type_coin_to_name(type_code):
        """
        通过code查找 中文含义
        :param: type_code 类型
        :return: 返回对象
        """
        sql = "select * from common_coin_type where type_code= '%s'" % type_code
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return result[0]
        else:
            raise ValueError("No Data!")

    @staticmethod
    def assert_broker_message_log(param_1, param_2, param_3):
        """
        如果是错误的断言，那么会在这个表中存在一条记录，所以需要断言这个表
        :param: param123 请选择三个较为关键和唯一的参数作为入参
        :return: 返回对象
        """
        sql = "select * from broker_message_log " \
              "where message like '%%%s%%' " \
              "and message like '%%%s%%' and message like '%%%s%%'" % (param_1, param_2, param_3)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    """
    积分微币账户初始化操作
    """

    @staticmethod
    def assert_init_coin(member_id, account_type, investment_id=0):
        """
        断言初始化账户
        :param member_id:
        :param account_type:
        :param investment_id:
        :return:
        """
        sql = "select * from common_coin_coin " \
              "where member_id = '%s' and account_type ='%s' and investment_id = '%s' " \
              "and status = 0 and total_coin = 0 and now_coin = 0 and freeze_coin = 0 " \
              "and withdraw_coin = 0 and today_increase = 0 and today_decrease = 0 and reserve_coin = 0 " \
              "and create_time is not NULL and update_time is not NULL " \
              "and version = 0 and remark is null " % (member_id, account_type, investment_id)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_init_credits(member_id, account_type, investment_id=0, level=10, transfer_date=None):
        """
        断言初始化账户
        :param member_id:
        :param account_type:
        :param investment_id:
        :param level:
        :param transfer_date:
        :return:
        """
        sql = "select * from common_coin_credits " \
              "where member_id = '%s' and account_type ='%s' and investment_id = '%s' " \
              "and status = 0 and total_credits = 0 and now_credits = 0 " \
              "and today_increase = 0 and today_decrease = 0 " \
              "and create_time is not NULL and update_time is not NULL " \
              "and remark is null and version = 0 and level ='%s' and transfer_date = '%s'" % (
                  member_id, account_type, investment_id, level, transfer_date)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def delete_init_account(member_id):
        """
        删除创建的账户
        :param member_id:
        :return:
        """
        sql = "delete from common_coin_coin where member_id = '%s'" % member_id
        my_db.execute_sql("coin", sql)
        sql = "delete from common_coin_credits where member_id = '%s'" % member_id
        my_db.execute_sql("coin", sql)

    @staticmethod
    def get_now_coin(member_id, account_type, investment_id=0):
        """
        根据三个信息获取用户详细的资产详情
        :param member_id:
        :param account_type: 积分账户类型 1：会员积分账户 2：投资积分账户 3：采购商积分账户
        :param investment_id: 默认为0
        :return: 返回用户对象信息
        """
        sql = "select * from common_coin_coin " \
              "where member_id ='%s' and account_type = '%s' and investment_id = '%s' " \
              "" % (member_id, account_type, investment_id)
        result = my_db.execute_select("coin", sql)
        if result[0]:
            return result[0][0]
        else:
            raise ValueError("Data Error!")

    @staticmethod
    def update_credits_transfer_and_clear(transfer_date, clear_date, member_id, account_type, investment_id=0):
        """
        更新转换时间和清理时间
        :param transfer_date:
        :param clear_date:
        :param member_id:
        :param account_type: 积分账户类型 1：会员积分账户 2：投资积分账户 3：采购商积分账户
        :param investment_id: 默认为0
        :return:
        """
        sql = "update common_coin_coin " \
              "set transfer_date='%s',clear_date='%s' " \
              "where  member_id = '%s' and account_type ='%s' and investment_id = '%s'" % (
                  transfer_date, clear_date, member_id, account_type, investment_id
              )
        result = my_db.execute_update("coin", sql)
        return result

    @staticmethod
    def update_coin_transfer_and_clear(transfer_date, clear_date, member_id, account_type, investment_id=0):
        """
        更新转换时间和清理时间
        :param transfer_date:
        :param clear_date:
        :param member_id:
        :param account_type: 积分账户类型 1：会员积分账户 2：投资积分账户 3：采购商积分账户
        :param investment_id: 默认为0
        :return:
        """
        sql = "update common_coin_credits " \
              "set transfer_date='%s',clear_date='%s' " \
              "where  member_id = '%s' and account_type ='%s' and investment_id = '%s'" % (
                  transfer_date, clear_date, member_id, account_type, investment_id
              )
        result = my_db.execute_update("coin", sql)
        return result

    @staticmethod
    def assert_common_coin_coin_task(member_id, account_type, investment_id, status, total_coin, now_coin, freeze_coin,
                                     withdraw_coin, today_increase, today_decrease, reserve_coin, version, clear_date,
                                     transfer_date):
        """
        积分转微币定时专用
        :param member_id:
        :param account_type:
        :param investment_id:
        :param status:
        :param total_coin:
        :param now_coin:
        :param freeze_coin:
        :param withdraw_coin:
        :param today_increase:
        :param today_decrease:
        :param reserve_coin:
        :param version:
        :param clear_date:
        :param transfer_date:
        :return:
        """
        sql = "select * from common_coin_coin " \
              "where member_id = '%s' and account_type = '%s' and investment_id = '%s' " \
              "and status = '%s' " \
              "and total_coin = '%s' and now_coin = '%s' and freeze_coin = '%s' and withdraw_coin = '%s' " \
              "and today_increase = '%s' and today_decrease = '%s' and reserve_coin = '%s' and clear_date = '%s' " \
              "and transfer_date='%s'" \
              "and create_time is not null and update_time is not NULL " \
              "and version = '%s'" \
              "" % (member_id, account_type, investment_id, status, total_coin, now_coin, freeze_coin,
                    withdraw_coin, today_increase, today_decrease, reserve_coin, clear_date, transfer_date, version)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def assert_common_credits_task(member_id, account_type, investment_id, status, total_credits, now_credits,
                                   today_increase, today_decrease, version, level, clear_date, transfer_date):
        """
        断言积分表common_coin_credits 积分转微币定时专用
        :param member_id:
        :param account_type:
        :param investment_id:
        :param status:
        :param total_credits:
        :param now_credits:
        :param today_increase:
        :param today_decrease:
        :param version:
        :param level:
        :param clear_date:
        :param transfer_date:
        :return: True or False
        """
        sql = "select * from common_coin_credits " \
              "where member_id = '%s' and account_type = '%s' and investment_id = '%s' " \
              "and status = '%s' and total_credits = '%s' and now_credits = '%s' " \
              "and today_increase = '%s' and today_decrease = '%s' " \
              "and create_time is not null and update_time is not NULL " \
              "and version = '%s' and level = '%s' and clear_date = '%s' and transfer_date = '%s'" \
              "" % (member_id, account_type, investment_id, status, total_credits, now_credits,
                    today_increase, today_decrease, version, level, clear_date, transfer_date)
        result = my_db.execute_select("coin", sql)
        if len(result) == 1:
            return True
        else:
            return False

    @staticmethod
    def get_credit_log_all_task(credit_id, create_time, operation_type=1):
        """
        :param credit_id:积分ID
        :param create_time:
        :param operation_type:1增加 -1减少
        :return:
        """
        if operation_type == 1:
            sql = "select ifnull(sum(credits),0) as total from common_coin_credits_log " \
                  "where credits_id='%s' and create_time>='%s' and credits>0" % (credit_id, create_time)
        elif operation_type == -1:
            sql = "select ifnull(sum(credits),0)*-1 as total from common_coin_credits_log " \
                  "where credits_id='%s' and create_time>='%s' and credits<0" % (credit_id, create_time)
        else:
            raise ValueError("operation_type有误")
        result = my_db.execute_select("coin", sql)
        return result[0].total

    @staticmethod
    def get_coin_log_all_task(coin_id, create_time, operation_type=1):
        """
        :param coin_id:
        :param create_time:
        :param operation_type:1增加 -1减少
        :return:
        """
        if operation_type == 1:
            sql = "select ifnull(sum(coin),0) as total from common_coin_coin_log " \
                  "where coin_id='%s' and create_time>='%s' and coin>0" % (coin_id, create_time)
        elif operation_type == -1:
            sql = "select ifnull(sum(coin),0)*-1 as total from common_coin_coin_log " \
                  "where coin_id='%s' and create_time>='%s' and coin<0" % (coin_id, create_time)
        else:
            raise ValueError("operation_type有误")
        result = my_db.execute_select("coin", sql)
        return result[0].total

    @staticmethod
    def all_data():
        sql = "SELECT a.ordersn,  a.type ,a.order_amount " \
              "FROM  `order`.life_order_order AS a  " \
              "LEFT JOIN member.life_member AS b ON b.id = a.member_id " \
              "WHERE  a.ordersn IN ( SELECT ordersn FROM finance.common_pay_charge_order " \
              "WHERE STATUS = 'paid')   " \
              "and a.id NOT in (SELECT order_id FROM `order`.life_order_refund)" \
              "  AND a.created_at >= '2020-06-05 14:44:00' " \
              "  AND a.STATUS IN (10, 15 )" \
              "ORDER BY" \
              "  a.type," \
              "  a.created_at"
        my_db = MyDB()
        result = my_db.execute_select('coin', sql)
        return result

    @staticmethod
    def order_verify(order_sn):
        sql = "select status from order.life_order_order where ordersn = '%s'" % order_sn
        result = my_db.execute_select('order', sql)
        return result

    @staticmethod
    def onorder_verify(order_sn):
        sql = "select * from onorder.life_online_orders where ordersn = '%s'" % order_sn
        result = my_db.execute_select('onorder', sql)
        return result

    @staticmethod
    def offorder_verify(order_sn):
        sql = "select * from offorder.life_offline_scan_code_orders where ordersn = '%s'" % order_sn
        result = my_db.execute_select('offorder', sql)
        return result

    @staticmethod
    def mobile_verify(order_sn):
        sql = "select * from third.life_third_mobile_recharge_orders where xb_order_sn = '%s'" % order_sn
        result = my_db.execute_select('third', sql)
        return result

    @staticmethod
    def credit_verify(order_sn, price):
        sql = "select * from coin.common_coin_credits_log " \
              "where order_no = '%s' and credits='%s'" % (order_sn, price)
        result = my_db.execute_select('coim', sql)
        if len(result) == 1:
            return True
        else:
            return False


if __name__ == '__main__':
    # test = DataBaseCoin()
    # a = test.all_data()
    # print(a)
    count = DataBaseCoin.filter_common_credits_account_info(357, 1)
    print(count.id)
